## 设备表与报警表数据绑定
import mysql.connector  # 或者 import PyMySQL  
from mysql.connector import Error  
import logging  
  
# 创建一个logger
logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(levelname)s - %(message)s')
logger = logging.getLogger(__name__)
logger.setLevel(logging.DEBUG)  #




def execute_query(query, cursor):  
    try:  
        cursor.execute(query)  
        return cursor.fetchall()  
    except Error as e:  
        print(f"Error executing query: {e}")  
        return None  
  
# 连接到MySQL数据库  
try:  
    connection = mysql.connector.connect(
        host='192.168.50.149',
        port=3306,
        database='hyzh-server',
        user='root',
        password='HYzh248713',
        connect_timeout=10  # 将10替换为所需的秒数
    )
    print("连接成功")
    if connection.is_connected():  
        print('Connected to MySQL database 33333')  
  
        cursor = connection.cursor()  
  
        # 步骤 2: 查询 t_gate_device 表  
        query = """  
        SELECT id, device_code, device_ip, device_name  
        FROM t_gate_device
        where 1=1
        and device_type = 2
        and status = 1
        and deleted = 0
        """

        logger.info("获取的所有设备列表")
        gate_devices = execute_query(query, cursor)  
        if gate_devices:  
            for device in gate_devices:  
                id, device_code, device_ip, device_name = device  
                # 假设您知道要更新的字段和值，这里只是一个示例  
                # 使用 UPDATE 语句批量更新 risk_hidden_danger_warning 表  
                # 这里的 `your_column_to_update` 是您想要更新的列，而 `'new_value'` 是新值  
                if  device_ip != '' and device_ip is not None:
                    update_query = f"""  
                    UPDATE risk_hidden_danger_warning SET relation_id ='{id}', device_code = '{device_code}' WHERE ip = '{device_ip}'  
                    """
                    logger.info(update_query)
                    execute_query(update_query, cursor)
                    connection.commit()  # 提交事务
  
        else:  
            print('No devices found in t_gate_device')  
  
except Error as e:  
    print(f"Error connecting to MySQL222: {e}")  
finally:  
    cursor.close()  
    connection.close()  
    print('MySQL connection is closed1111')